package com.qc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.qc.common.Constants;
import com.qc.common.DBHandler;
import com.qc.common.DBUtil;
import com.qc.common.DateUtil;
import com.qc.common.StringUtil;
import com.qc.dao.OrderDao;
import com.qc.entity.OrderAlipayCallBackEntity;
import com.qc.entity.OrderBillEntity;
import com.qc.entity.OrderCommentEntity;
import com.qc.entity.OrderEntity;
import com.qc.entity.OrderEntityVo;
import com.qc.entity.OrderScheduleEntity;
import com.qc.entity.info.MyOrderInfo;

@Repository
@SuppressWarnings({ "unchecked", "rawtypes" })
public class OrderDaoImpl implements OrderDao {

	@Resource
	private DataSource dataSource;
	
	@Resource(name = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;
	
	@Override
	public long addOrder(OrderEntity entity) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getBossUserId());
		l.add(entity.getSecretaryUserId());
		l.add(entity.getOrderNumber());
		l.add(entity.getTag());
		l.add(entity.getStartDate());
		l.add(entity.getEndDate());
		l.add(entity.getShopMark());
		l.add(entity.getShopProvince());
		l.add(entity.getShopCity());
		l.add(entity.getShopDistrict());
		l.add(entity.getShopAddress());
		l.add(entity.getShopLatitude());
		l.add(entity.getShopLongitude());
		l.add(entity.getCuId());
//		l.add(entity.getOrderState());
		l.add(entity.getMoney());
		l.add(entity.getIsPay());
		l.add(entity.getOrderNumberDays());
		l.add(entity.getTimeDay());

		long i = 0;
		try {
			
			i = dbHandler.update(DBUtil.getSql(Constants.SAVE_ORDER), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public OrderEntity getOrderByOrderNumber(String orderNumber) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(orderNumber);
		try {
			dbHandler.rs = dbHandler.query(DBUtil.getSql(Constants.SELECT_ORDER_BY_ORDERNUMBER), l, dataSource);
			if(dbHandler.rs.next()){

				OrderEntity entity = OrderEntity.getInstance(dbHandler.rs);
				return entity;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public long updateOrderIsPay(long orderId, int payType) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(payType);
		l.add(orderId);

		long i = 0;
		try {
			
			i = dbHandler.update(DBUtil.getSql(Constants.UPDATE_ORDER_ISPAY), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public long addOrderPayLog(OrderAlipayCallBackEntity entity) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getPayUserId());
		l.add(entity.getPayType());
		l.add(entity.getOutTradeNo());
		l.add(entity.getTotalAmount());
		l.add(entity.getReceiptAmount());
		l.add(entity.getSign());
		l.add(entity.getGmtPayment());
		l.add(entity.getBuyerId());
		l.add(entity.getSubject());
		l.add(entity.getTradeStatus());
		long i = 0;
		try {
			
			i = dbHandler.update(DBUtil.getSql(Constants.SAVE_ORDER_PAY_LOG), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public long addOrderBill(OrderBillEntity entity) {
		//insert into qc.t_order_bill(order_id, ob_type, ob_name, receive_username, receive_mobile,receive_province,
		//receive_city, receive_address_detail, logistics_type)
//	values (?,?,?,?,?,?,?,?,?)
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getOrderId());
		l.add(entity.getObType());
		l.add(entity.getObName());
		l.add(entity.getReceiveUsername());
		l.add(entity.getReceiveMobile());
		l.add(entity.getReceiveProvince());
		l.add(entity.getReceiveCity());
		l.add(entity.getReceiveAddressDetail());
		l.add(entity.getLogisticsType());
		long i = 0;
		try {
			
			i = dbHandler.update(DBUtil.getSql(Constants.SAVE_ORDER_BILL), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public List<OrderEntity> getAllMyOrder(MyOrderInfo orderInfo, long userId) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(userId);
		
		List<OrderEntity> list = new ArrayList<OrderEntity>();
		try {
			StringBuilder sb = new StringBuilder();
			
			if (orderInfo.getIsBoss() == 1) {
				//BOSS身份拿我的聘单
				sb.append(DBUtil.getSql(Constants.SELECT_BOSS_MY_ORDER)); 
			} else if (orderInfo.getIsBoss() == 2) {
				//秘书身份拿我的约单
				sb.append(DBUtil.getSql(Constants.SELECT_SECRETARY_MY_ORDER));
//				dbHandler.dbHandler.rs = dbHandler.query(DBUtil.getSql(Constants.SELECT_SECRETARY_MY_ORDER), l, dataSource);
			}
			if (orderInfo.getState() == 2) {
				sb.append(" and (o.order_state = 1 or o.order_state = 2 or o.order_state = 3)");
			} else if (orderInfo.getState() == 3) {
				sb.append(" and o.order_state = 4 and is_pay = 1");
			}
			sb.append(" order by o.order_id desc");
			
			dbHandler.rs = dbHandler.query(sb.toString(), l, dataSource);
			while(dbHandler.rs.next()){

				OrderEntity entity = OrderEntity.getInstance(dbHandler.rs);
				entity.setBossNickname(dbHandler.rs.getString("bossNickname"));
				entity.setBossAvatar(dbHandler.rs.getString("bossAvatar"));
				list.add(entity);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public long updateOrderState(int orderState,long orderId) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(orderState);
		l.add(orderId);
		
		long i = 0;
		try {
			
			i = dbHandler.update(DBUtil.getSql(Constants.UPDATE_ORDER_STATE), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public OrderEntity getBothOrderInfo(OrderEntity entity) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getBossUserId());
		l.add(entity.getSecretaryUserId());
		l.add(entity.getSecretaryUserId());
		l.add(entity.getBossUserId());
		
		try {
			dbHandler.rs = dbHandler.query(DBUtil.getSql(Constants.SELECT_BOTH_ORDER), l, dataSource);
			if(dbHandler.rs.next()){

				OrderEntity e = OrderEntity.getInstance(dbHandler.rs);
				e.setBossOrdersState(dbHandler.rs.getInt("bossOrdersState"));
				e.setSecretaryOrdersState(dbHandler.rs.getInt("secretaryOrdersState"));
				return e;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public long addOrderSchedule(OrderScheduleEntity entity) {
		DBHandler dbHandler = new DBHandler();
		//order_id, add_user_id,start_time, title, shop_mark,shop_province,shop_city,
		//shop_district,shop_address,shop_latitude,shop_longitude, detail)
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getOrderId());
		l.add(entity.getAddUserId());
		l.add(entity.getStartTime());
		l.add(entity.getDate());
		l.add(entity.getShortTime());
		l.add(entity.getTitle());
		l.add(entity.getShopMark());
		l.add(entity.getShopProvince());
		l.add(entity.getShopCity());
		l.add(entity.getShopDistrict());
		l.add(entity.getShopAddress());
		l.add(entity.getShopLatitude());
		l.add(entity.getShopLongitude());
		l.add(entity.getDetail());
		
		long i = 0;
		try {
			
			i = dbHandler.update(DBUtil.getSql(Constants.SAVE_ORDER_SCHEDULE), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public List<OrderScheduleEntity> getScheduleByOrderIdAndDate(OrderScheduleEntity entity) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getDate());
		l.add(entity.getOrderId());
		
		List<OrderScheduleEntity> list = new ArrayList<OrderScheduleEntity>();
		try {
			dbHandler.rs = dbHandler.query(DBUtil.getSql(Constants.SELECT_ORDER_SCHEDULE_BY_DATE), l, dataSource);
			while(dbHandler.rs.next()){

				OrderScheduleEntity e = OrderScheduleEntity.getInstance(dbHandler.rs);
				list.add(e);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public long cancelOrder(long orderId) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(DateUtil.getCurrentSysTimestamp());
		l.add(orderId);
		
		long i = 0;
		try {
			
			i = dbHandler.update(DBUtil.getSql(Constants.UPDATE_ORDER_ISDELETED), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public long addOrderComment(OrderCommentEntity entity) {
		DBHandler dbHandler = new DBHandler();
		//sender_user_id,receive_user_id,order_id,grade,tag,oc_content
		List<Object> l = new ArrayList<Object>();
		l.add(entity.getSenderUserId());
		l.add(entity.getReceiveUserId());
		l.add(entity.getOrderId());
		l.add(entity.getGrade());
		l.add(entity.getTag());
		l.add(entity.getOcContent());
		
		long i = 0;
		try {
			
			i = dbHandler.update(DBUtil.getSql(Constants.SAVE_ORDER_COMMENT), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public long updateOrderIsComment(long orderId) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(orderId);
		
		long i = 0;
		try {
			
			i = dbHandler.update(DBUtil.getSql(Constants.UPDATE_ORDER_ISCOMMENT), l, dataSource);
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public List<OrderCommentEntity> getOrderCommentByOrderId(long orderId) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(orderId);
		
		List<OrderCommentEntity> list = new ArrayList<OrderCommentEntity>();
		try {
			dbHandler.rs = dbHandler.query(DBUtil.getSql(Constants.GET_ORDER_COMMENT), l, dataSource);
			while(dbHandler.rs.next()){

				OrderCommentEntity e = OrderCommentEntity.getInstance(dbHandler.rs);
				list.add(e);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

	@Override
	public OrderEntity getOrderDetailByOrderId(long orderId) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(orderId);
		try {
			dbHandler.rs = dbHandler.query(DBUtil.getSql(Constants.GET_ORDER_DETAIL_BYORDERID), l, dataSource);
			if(dbHandler.rs.next()){

				OrderEntity e = OrderEntity.getInstance(dbHandler.rs);
				e.setBossNickname(dbHandler.rs.getString("bossNickname"));
				e.setBossAvatar(dbHandler.rs.getString("bossAvatar"));
				OrderBillEntity b = OrderBillEntity.getInstance(dbHandler.rs);
				e.setGmtPayment(dbHandler.rs.getString("gmt_payment"));
				e.setBillEntity(b);
				return e;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

    @Override
    public int getListOrderCount(String bNickName,String mNickName,String orderNumber,String orderState,String isPay) {
        String sql = "select count(*) from t_order a "
                + " left join t_user u on u.user_id = a.boss_user_id"
                + " left join t_user b on b.user_id = a.secretary_user_id "
                + " where 1 = 1 ";
        
            if (StringUtil.isNotEmpty(bNickName) && !"undefined".equals(bNickName) ) {
                sql += " and u.nickname like '%"+bNickName+"%' ";
            }
            
            if (StringUtil.isNotEmpty(mNickName) && !"undefined".equals(mNickName) ) {
                sql += " and b.nickname like '%"+mNickName+"%' ";
            }
            
            if (StringUtil.isNotEmpty(orderNumber) && !"undefined".equals(orderNumber) ) {
                sql += " and a.order_number like '%"+orderNumber+"%' ";
            }
            
            if (StringUtil.isNotEmpty(orderState) && !"undefined".equals(orderState) ) {
                sql += " and a.order_state = '"+orderState+"' ";
            }
            
            if (StringUtil.isNotEmpty(isPay) && !"undefined".equals(isPay) ) {
                sql += " and a.is_pay = '"+isPay+"' ";
            }
            
            return jdbcTemplate.queryForInt(sql);
    }

    @SuppressWarnings("rawtypes")
    @Override
    public List getAllOrder(String bNickName,String mNickName,String orderNumber,String orderState,String isPay, int pageNo, int pageSize) {
        
        int beginRow = (pageNo - 1)* pageSize;
        
        String sql = "select a.order_id,"
                + " u.nickname as bNickName,"
                + " b.nickname as mNickName,"
                + " a.order_number,"
                + " a.created_time,"
                + " a.money,"
                + " a.order_days,"
                + " a.start_date,"
                + " a.end_date,"
                + " a.order_state,"
                + " a.is_pay"
                + " from t_order a "
                + " left join t_user u on u.user_id = a.boss_user_id"
                + " left join t_user b on b.user_id = a.secretary_user_id "
                + " where 1 = 1 ";
        
            if (StringUtil.isNotEmpty(bNickName) && !"undefined".equals(bNickName) ) {
                sql += " and u.nickname like '%"+bNickName+"%' ";
            }
            
            if (StringUtil.isNotEmpty(mNickName) && !"undefined".equals(mNickName) ) {
                sql += " and b.nickname like '%"+mNickName+"%' ";
            }
            
            if (StringUtil.isNotEmpty(orderNumber) && !"undefined".equals(orderNumber) ) {
                sql += " and a.order_number like '%"+orderNumber+"%' ";
            }
            
            if (StringUtil.isNotEmpty(orderState) && !"undefined".equals(orderState) ) {
                sql += " and a.order_state = '"+orderState+"' ";
            }
            
            if (StringUtil.isNotEmpty(isPay) && !"undefined".equals(isPay) ) {
                sql += " and a.is_pay = '"+isPay+"' ";
            }
            
            sql += " order by a.created_time DESC ";
            sql += " LIMIT "+ String.valueOf(beginRow)+ "," + pageSize;
            
            return jdbcTemplate.queryForList(sql);
    }

    @Override
    public OrderEntityVo getOrderId(String orderId) {
        
        String sql = "select a.order_id,"
                + " a.order_number,"
                + " a.order_state,"
                + " a.money,"
                
                + " a.pay_type,"
                + " a.order_days,"
                + " a.shop_province,"
                + " a.shop_city,"
                + " a.shop_district,"
                + " a.shop_address,"
                + " a.start_date,"
                + " a.end_date,"
                + " a.cancel_time,"
                + " c.ob_type,"
                + " c.ob_name,"
                
                + " u.user_name,"
                + " u.mobile,"
                
                + " b.user_name as mUserName,"
                + " b.mobile as mMobile,"
                
                + " d.money_day"
                
                + " from t_order a "
                + " left join t_user u on u.user_id = a.boss_user_id"
                + " left join t_user b on b.user_id = a.secretary_user_id "
                + " left join t_order_bill c on c.order_id = a.order_id"
                + " left join t_user_secretary d on d.user_id = a.secretary_user_id"
                + " where 1 = 1 ";
        
        if (StringUtil.isNotEmpty(orderId) && !"undefined".equals(orderId) ) {
            sql += " and a.order_id = '"+orderId+"' ";
        }
        
        OrderEntityVo orderEntityVo = new OrderEntityVo();
        jdbcTemplate.query(sql, new RowMapper<OrderEntityVo>() {
            public OrderEntityVo mapRow(ResultSet rs, int rowNum) throws SQLException {
                
                orderEntityVo.setOrderId(rs.getLong("order_id"));
                orderEntityVo.setOrderNumber(rs.getString("order_number"));
                orderEntityVo.setOrderState(rs.getInt("order_state"));
                orderEntityVo.setMoney(rs.getDouble("money"));
                
                orderEntityVo.setPayType(rs.getInt("pay_type"));
                orderEntityVo.setOrderDays(rs.getLong("order_days"));
                orderEntityVo.setShopProvince(rs.getString("shop_province"));
                orderEntityVo.setShopCity(rs.getString("shop_city"));
                orderEntityVo.setShopDistrict(rs.getString("shop_district"));
                orderEntityVo.setShopAddress(rs.getString("shop_address"));
                orderEntityVo.setStartDate(rs.getString("start_date"));
                orderEntityVo.setEndDate(rs.getString("end_date"));
                orderEntityVo.setCancelTime(rs.getString("cancel_time"));
                orderEntityVo.setObType(rs.getInt("ob_type"));
                orderEntityVo.setObName(rs.getString("ob_name"));
                
                orderEntityVo.setUserName(rs.getString("user_name"));
                orderEntityVo.setMobile(rs.getString("mobile"));
                
                orderEntityVo.setmUserName(rs.getString("mUserName"));
                orderEntityVo.setmMobile(rs.getString("mMobile"));
                orderEntityVo.setMoneyDay(rs.getString("money_day"));
                
                return orderEntityVo;
            }
        });
        
        return orderEntityVo;
    }

    @Override
    public List getOrderScheduleId(String orderId) {
        String sql = "select "
                + " a.start_time,"
                + " a.shop_province,"
                + " a.shop_city,"
                + " a.shop_district,"
                + " a.shop_address,"
                + " a.detail "
                + " from t_order_schedule a"
                + " where 1 = 1 ";
        
            if (StringUtil.isNotEmpty(orderId) && !"undefined".equals(orderId) ) {
                sql += " and a.order_id = '"+orderId+"' ";
            }
            
            return jdbcTemplate.queryForList(sql);
    }

	@Override
	public List<OrderEntity> getSecretaryBySecUserId(long userId) {
		DBHandler dbHandler = new DBHandler();
		List<Object> l = new ArrayList<Object>();
		l.add(userId);
		
		List<OrderEntity> list = new ArrayList<OrderEntity>();
		try {
			dbHandler.rs = dbHandler.query(DBUtil.getSql(Constants.SELECT_SECRETARY_ORDER), l, dataSource);
			while(dbHandler.rs.next()){

				OrderEntity entity = OrderEntity.getInstance(dbHandler.rs);
				list.add(entity);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			dbHandler.closeAll();
		}
		return null;
	}

    @Override
    public List<OrderScheduleEntity> getAllOrderByDate(String startTime, String endTime) {
        String sql = "select "
                + " a.order_schedule_id, a.order_id,a.title,a.short_time,"
                + " a.start_time,"
                + " b.boss_user_id"
                + " from t_order_schedule a "
                + " left join t_order b on b.order_id = a.order_id"
                + " where 1 = 1 and a.start_time BETWEEN '"+startTime+"' and '"+endTime+"' and a.is_push = 0 and b.is_pay = 1 and b.order_state = 3 and b.is_deleted = 0 ";
        
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper(OrderScheduleEntity.class));
    }

    @Override
    public List<OrderEntity> getAllOrderTime(String startDate, String endDate) {
        
        String sql = "select a.order_id,a.order_state,a.start_date,a.end_date,a.secretary_user_id,a.boss_user_id,a.money from t_order a where a.is_deleted = 0 and a.is_pay = 1 ";
        
            if (StringUtil.isNotEmpty(startDate)) {
                sql += " and a.order_state = 2 and a.start_date = '"+startDate+"'";
            }
            
            if (StringUtil.isNotEmpty(endDate)) {
                sql += " and a.order_state = 3 and a.end_date = '"+endDate+"'";
                
            }
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper(OrderEntity.class));
    }

    @Override
    public void updateOrderState(String orderId,String orderState) {

        String sql = "update t_order set order_state = '"+orderState+"' where order_id = " +orderId;
        jdbcTemplate.update(sql);
    }

	@Override
	public void updateOrderSchedulePush(long orderScheduleId) {
		
		String sql = "update qc.t_order_schedule set is_push = 1 where order_schedule_id = "+orderScheduleId;
        jdbcTemplate.update(sql);
	}

	
}
